Split digiKam database queries between localhost (thumbnails)
and network (metadata)

The aim and advantage of splitting the database is to have rapid image browsing through the local thumbnail DB and metadata on the server, where it can be shared between several users.

homepage: ProxySQL

proxysql allows you to redirect a user's SQL queries to different SQL servers, by defining rules that match query patterns. In my digiKam usecase the rules are fairly simple, since all commands involving the thumbnails database digikam_thumbs should be redirected to a local MySQL server, and all calls to the digikam database redirected to the network server. Still, the setup is time-consuming, and the complexity of at least two databases in two MySQL instances, in addition to the ProxySQL instance, can lead to confusion. I'm using MariaDB from the respective Linux distribution both an the network server (Debian), and on the clients. A user for ProxySQL has to be created on all these databases, and given the correct access rights.

On the local server:

 
      sudo su 
      mysql

      mysql>
      use mysql;
      CREATE USER 'dkproxy'@'localhost' IDENTIFIED BY 'yourPassword'; select User,Password from user;
      GRANT ALL ON digikam_thumbs.* TO 'dkproxy'@'localhost'; 
      quit;>
      # mysql -p -u root digikam_thumbs < digikam_thumbs.sql;

And on the network server 

 
      # CREATE DATABASE digikam; (same for face and similarity)>
      # mysql -p -u root digikam < digikam.sql; (same for face and similarity)
      CREATE USER 'dkproxy'@'%' IDENTIFIED BY 'yourPassword'; 
      GRANT ALL ON digikam.* TO 'dkproxy'@'%';

On the local ProxySQL server:


      sudo dpkg -i proxysql_2.0.10-ubuntu18_amd64.deb
      mysql -u admin -p -h 127.0.0.1 -P6032 --prompt='Admin> '
      Admin>
      use main;

Now create the user dkproxy, and the rules for redirecting queries:


      INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('dkproxy','yourPassword', 1);
      SAVE MYSQL USERS TO DISK; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME;; LOAD MYSQL USERS TO RUNTIME;

      INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'127.0.0.1',3306);
      INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'network server',3306);
      SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME;

Digikam needs a socket for working with proxySQL


  UPDATE global_variables set variable_value='0.0.0.0:6033;/run/proxysql/proxysql.sock' WHERE variable_name='mysql-interfaces';
  INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (10,1,'dkproxy','digikam_thumbs',0,1);
  INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (20,1,'dkproxy','digikam',1,1);
  INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (21,1,'dkproxy','digikam_face',1,1);
  INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,destination_hostgroup,apply) values (22,1,'dkproxy','digikam_similarity',1,1);
  SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME;>

  SELECT rule_id,active,username,schemaname,destination_hostgroup,apply FROM mysql_query_rules;
+---------+--------+----------+--------------------+-----------------------+-------+
| rule_id | active | username | schemaname         | destination_hostgroup | apply |
+---------+--------+----------+--------------------+-----------------------+-------+
| 10      | 1      | dkproxy  | digikam_thumbs     | 0                     | 1     |
| 20      | 1      | dkproxy  | digikam            | 1                     | 1     |
| 21      | 1      | dkproxy  | digikam_face       | 1                     | 1     |
| 22      | 1      | dkproxy  | digikam_similarity | 1                     | 1     |
| 23      | 1      | NULL     | NULL               | NULL                  | 1     |
+---------+--------+----------+--------------------+-----------------------+-------+

That should be all, if I did not forget anything. Again connecting to the ProxySQL admin interface you can easily see how well the rules are working:


      Admin> 
      SELECT hostgroup,srv_host,status,ConnFree,ConnOK,Queries,Bytes_data_sent,Bytes_data_recv,Latency_us FROM stats_mysql_connection_pool;
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host        | srv_port | status | ConnFree | ConnOK | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
| 0         | 127.0.0.1       | 3306     | ONLINE | 1        | 1      | 34767   | 2128098         | 595568549       | 0          |
| 1         | 192.168.1.180   | 3306     | ONLINE | 2        | 2      | 121759  | 3399218         | 5250558         | 380        |
+-----------+-----------------+----------+--------+----------+--------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)

Digikam must be configured now. Hostport must be 6033. As DB connection options, addition 

   UNIX_SOCKET=/run/proxysql/proxysql.sock

From our local thumbnail host we got about 100 times as much data as from the general digiKam database, which is on the network. But most importantly: digiKam feels much faster again! Next if I have time I might look into why digiKam sent so many queries to the network server after all, when I just clicked around and filtered for a few minutes! ;-) In my previous job I could reduce the number of SQL queries in a database application drastically by using more JOIN statements...

Migrating thumbnail DB to local server

On the server with the thumbnails:

 
      mysqldump -u root -x -e --databases digikam_thumbs > thumbs.sql

Then copy the data e.g through scp to your local machine:

 
      mysql < thumbs.sql